﻿using System;

namespace Marmat.Data
{
    /// <summary>
    /// 生成SQL加载语句的类
    /// </summary>
    /// <remarks></remarks>
    public class DataTablePageHelper
    {

        /// <summary>
        /// 获取或设置数据表的名称
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string TableName { get; set; }

        /// <summary>
        /// 字符型，用于设置重新加载数据的条件表达式，语法和所使用的数据源有关。
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string Filter { get; set; }

        /// <summary>
        /// 获取或设置需要加载的列
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string Columns { get; set; }


        /// <summary>
        /// 字符型，指定加载数据的顺序。
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string Order { get; set; }

        /// <summary>
        /// 整数型，指定要加载的行数，如果是分页加载，则用于指定每页的行数。
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public int Top { get; set; }

        /// <summary>
        /// 逻辑型，是否倒序加载，即是否先加载新数据，此属性只有在分页加载的时候才有效。
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public bool Reverse { get; set; }

        /// <summary>
        /// 字符型，用于指定分页加载依据列。
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string Over { get; set; }

        /// <summary>
        /// 整数型，返回分页加载时，总的可加载总页数。
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public int Pages
        {
            get
            {
                return -1;
            }
        }

        /// <summary>
        /// 得到SQL语句
        /// </summary>
        /// <param name="LoadPage">整数型，指定要加载的页号。当值为-1时，则加载全部数据</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public string GetSqlString(int LoadPage)
        {
            string Sql = null;
            string ORDER_BY = this.Order != "GETDATE()" ? this.Order : !string.IsNullOrEmpty(this.Over) ? this.Over : null;
            if (!string.IsNullOrEmpty(ORDER_BY))
            {
                ORDER_BY = "ORDER BY " + ORDER_BY + (this.Reverse ? " DESC" : null);
            }
            if (LoadPage == 0)
            {
                if (string.IsNullOrEmpty(this.Over))
                    throw new Exception("分页加载必须指定依据列。");
                Sql = $"SELECT Top {this.Top} {this.Columns} FROM [{this.TableName}] {(!string.IsNullOrEmpty(this.Filter) ? "WHERE " + this.Filter : "")} {ORDER_BY}";
            }
            else if (LoadPage > 0)
            {
                if (string.IsNullOrEmpty(this.Over))
                    throw new Exception("分页加载必须指定依据列。");
                string LoadFilter = !string.IsNullOrEmpty(this.Filter) ? this.Filter + " AND " : ""; // 4加载条件 & " AND ",如果为空的话就直接返回空
                int LoadPosition = this.Top * LoadPage; // 6符合当前条件的第N页记录的起始行
                string LoadReverse = this.Reverse ? "DESC" : ""; // 7是否倒序加载，是则返回： DESC,否则返回：""
                string LoadOrder = this.Order != "GETDATE()" ? this.Order : this.Over; // 8加载排序列，如果为空的话，则返回依据列
                string ReverseBoo = this.Reverse ? "<" : ">"; // 9加载逻辑，默认加载返回>,倒序加载返回<
                string ReverseStr = this.Reverse ? "MIN" : "MAX"; // 10加载逻辑，默认加载返回MAX,倒序加载返回MID
                Sql = $"SELECT TOP {this.Top} {this.Columns} FROM [{this.TableName}] WHERE ({LoadFilter} {this.Over} {LoadOrder}(SELECT {ReverseBoo}({LoadFilter}) FROM (SELECT TOP {LoadPosition} {LoadFilter} FROM [{this.TableName}] { (!string.IsNullOrEmpty(this.Filter) ? " Where " + this.Filter : "")} ORDER BY {this.Over} {LoadReverse}) AS A)) ORDER BY {LoadOrder} {LoadReverse}";
            }
            else if (LoadPage == -1)
            {
                Sql = $"SELECT {this.Columns} FROM [{this.TableName}] {(!string.IsNullOrEmpty(this.Filter) ? "WHERE " + this.Filter : "")} {ORDER_BY}";
            }

            return Sql;
        }

        /// <summary>
        /// 得到SQL语句
        /// </summary>
        /// <param name="LoadPage">整数型，指定要加载的页号。当值为-1时，则加载全部数据。</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public string GetSql2000(int LoadPage)
        {
            return this.GetSqlString(LoadPage);
        }

        /// <summary>
        /// 得到SQL语句
        /// </summary>
        /// <param name="LoadPage">整数型，指定要加载的页号。当值为-1时，则加载全部数据。</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public string GetSql2005(int LoadPage)
        {
            string Sql = null;
            string ORDER_BY = this.Order != "GETDATE()" ? this.Order : !string.IsNullOrEmpty(this.Over) ? this.Over : "";
            if (!string.IsNullOrEmpty(ORDER_BY))
            {
                ORDER_BY = "ORDER BY " + ORDER_BY + (this.Reverse ? " DESC" : "");
            }
            if (LoadPage == 0)
            {
                if (string.IsNullOrEmpty(this.Over))
                    throw new Exception("分页加载必须指定依据列。");
                Sql = $"SELECT Top {this.Top} {this.Columns} FROM [{this.TableName}] {(!string.IsNullOrEmpty(this.Filter) ? "WHERE " + this.Filter : "")} {ORDER_BY}";
            }
            else if (LoadPage > 0)
            {
                if (string.IsNullOrEmpty(this.Over))
                    throw new Exception("分页加载必须指定依据列。");
                string LoadFilter = !string.IsNullOrEmpty(this.Filter) ? "WHERE " + this.Filter : "";
                int StartTop = LoadPage * this.Top;
                int EndTop = (LoadPage + 1) * this.Top;
                Sql = $"SELECT {this.Columns} FROM (SELECT ROW_NUMBER() OVER ({ORDER_BY}) AS RowNumber,* FROM [{this.TableName}] {LoadFilter}) A WHERE RowNumber > {StartTop} AND RowNumber <= {EndTop}";
            }
            else if (LoadPage == -1)
            {
                Sql = $"SELECT {this.Columns} FROM [{this.TableName}] {(!string.IsNullOrEmpty(this.Filter) ? "WHERE " + this.Filter : "")} {ORDER_BY}";
            }
            return Sql;
        }
    }
}